<?php
/**
 * Mysql类库
 *
 */
class Helper_MysqlDb
{
	protected $db;

	public function __construct( $config )
	{
	    $this->db = mysql_connect( "{$config['host']}:{$config['port']}" , $config['user'] , $config['passwd'] , true , MYSQL_CLIENT_INTERACTIVE );
            //print_r($this->db);
		if( $this->db )
		{
			mysql_query( "SET NAMES 'utf8'" );
			if( isset( $config['name'] ) )
			{
				return mysql_select_db( $config['name'], $this->db );
			}

			return $this->db;
		}
		else
		{
			return false;
		}
	}

	public function selectDb( $name )
	{
		mysql_select_db( $name, $this->db );
	}

	public function query( $sql )
	{
		$ret =  mysql_query( $sql , $this->db );
		return $ret;
	}

	/**
	 * 获取一条记录
	 *
	 * @param string $table
	 * @param array $where
	 * @return array
	 */
	public function get( $table , $where , $columns = null )
	{
        if( $columns === null )
        {
         
            $columnList = '*';
        }
        elseif( !is_array( $columns ) )
        {
            throw new AppException( 303 );
        }
        else
        {
            $columnList = self::_makeColumn( $columns );
        }

		$sql = "SELECT {$columnList} FROM `{$table}` WHERE " . self::_makeWhere( $where ) . " LIMIT 1";
//		echo $sql;
		return $this->find( $sql );
	}

	/**
	 * 获取多条记录
	 * @param string $table
	 * @param array | string $where
	 * @param array $order
	 * @param int $limit
	 * @param int $start
	 * @return array
	 */
	public function getList( $table , $where , $order = array() , $start = 0 , $limit = 0 )
	{
		$sql = "SELECT * FROM `{$table}` WHERE " . self::_makeWhere( $where );
                
		if( $order )
		{
			$sql .= " ORDER BY " . self::_makeOrderValue( $order );
		}

		if( $limit > 0 )
		{
			$sql .= " LIMIT {$start} , {$limit}";
		}
		//echo $sql;
		return	$this->findAll( $sql );
                
	}
        /**
         * 查询多条记录（针对同一个字段）
         * 
         */
        public function getMultiList( $table , $column , $where , $start = 0, $limit = 0){
            
               $sql = "SELECT * FROM `{$table}` WHERE " . self::_columnWhere( $column,$where );
 
		if( $limit > 0 )
		{
			$sql .= " LIMIT {$start} , {$limit}";
		} 
                //echo $sql;
		return	$this->findAll( $sql );
            
        }
     

	/**
	 * 删除
	 * @param string $table
	 * @param array $where
	 * @param int $limit
	 * @return int
	 */
	public function delete( $table , $where , $limit = 0 )
	{
		$sql = "DELETE FROM `{$table}` WHERE " . self::_makeWhere(  $where );
		if( $limit > 0 )
		{
			$sql .= " LIMIT {$limit}";
		}

		$this->query( $sql );
		return $this->affectedRows();
	}

	/**
	 * 添加一条记录
	 * @param string $table
	 * @param array $value
	 * @return boolean
	 */
	public function add( $table , $value )
	{
		$sql = "INSERT INTO `{$table}` " . self::_makeInsertValue( $value );
                echo $sql;
		$this->query( $sql );
		return $this->affectedRows();
	}

	/**
	 * 更新一条记录
	 * @param string $table
	 * @param array $where
	 * @param array $value
	 * @return int
	 */
	public function update( $table , $where , $value , $limit = 0 )
	{
		$sql = "UPDATE `{$table}` SET " . self::_makeUpdateValue( $value ) . " WHERE " . self::_makeWhere( $where );
		if( $limit > 0 )
		{
			$sql .= " LIMIT {$limit}";
		}

		$this->query( $sql );
		return $this->affectedRows();
	}

	/**
	 * 获取多条记录
	 *
	 * @param string $sql
	 * @return array
	 */
	public function findAll( $sql )
	{
		$result = $this->query($sql);
		//print_r($result);
		return $this->res2Assoc($result);
	}

	/**
	 * 获取单条记录
	 *
	 * @param string $query
	 * @return array
	 */
	public function find( $query )
	{
		$result = $this->query($query);
		return $this->fetchAssoc($result);
	}

	public function fetchAssoc($result)
	{
		return mysql_fetch_assoc($result);
	}

	/**
	 * 获取影响行数
	 *
	 * @return int
	 */
	public function affectedRows()
	{
		return mysql_affected_rows($this->db);
	}

	/**
	 * 获取Insert ID
	 *
	 * @return int
	 */
	public function insertId()
	{
		return mysql_insert_id($this->db);
	}

	/**
	 * 获取记录数量
	 *
	 * @param array $where
	 * @return int
	 */
	public function getCount( $tables, $where = array() )
	{
		$sql = "SELECT COUNT(*) AS `count` FROM `{$tables}`";
		if( $where )
		{
			$sql .= " WHERE " . self::_makeWhere( $where );
		}
                

		$data = $this->find( $sql );
               
		return $data['count'];
	}

	public function & res2Assoc(& $res)
	{
		$rows = array();
		while( $row = $this->fetchAssoc( $res ) )
		{
			$rows[] = $row;
		}
		return $rows;
	}

	public function startTransaction()
	{
		mysql_query( "SET AUTOCOMMIT=0" , $this->db );
		mysql_query( "START TRANSACTION" , $this->db );
	}

	public function commitTransaction()
	{
		mysql_query( "COMMIT" );
		mysql_query( "SET AUTOCOMMIT=1" , $this->db );
	}

	public function rollbackTransaction()
	{
		mysql_query( "ROLLBACK" , $this->db );
	}

	/**
	 * 获取错误号码
	 *
	 * @return integer
	 */
	public function getErrorNumber()
	{
		return mysql_errno( $this->db );
	}

	/**
	 * 获取错误消息
	 *
	 * @return string
	 */
	public function getErrorMessage()
	{
		return mysql_error( $this->db );
	}

    /**
     * 拼接列语句
     *
     * @param array $columns
     * @return string
     */
    public static function _makeColumn( $columns )
    {
        $columList = array();
        foreach( $columns as $column )
        {
            $columList[] = "`{$column}`";
        }

        return implode( " , " , $columList );
    }
    /**
     * 拼接where语句
     * @param type $column
     * @param type $where 
     * 
     */
       public function _columnWhere( $column, $where ){
           foreach($where as $value){
               $columnList[] = "`{$column}` = {$value}"; 
           }
          // print_r($columnList);
           return  implode( '  ||  ' , $columnList);
        }
       

	/**
	 * 拼接条件语句
	 *
	 * @param array | string $where
	 * @return string
	 */
	public static function _makeWhere( $where )
	{
        if ( is_array( $where ) )
        {
            $whereList = array();
            foreach( $where as $key => $value )
            {
                if( is_numeric(  $key ) )
                {
                    $whereList[] = $value;
                }
                else
                {
                    $whereList[] = "`{$key}` = '{$value}'";
                }
            }

            $where = implode( " && " , $whereList );
        }
		return $where;
	}

	/**
	 * 拼接更新SQL
	 *
	 * @param array $data
	 * @return string
	 */
	public static function _makeUpdateValue( $data )
	{
        if( is_string( $data ) )
        {
            return $data;
        }
        
		$dataList = array();
		foreach( $data as $key => $value )
		{
			$dataList[] = "`{$key}` = '{$value}'";
		}

		return implode( " , ", $dataList );

	}

	/**
	 * 拼接更新SQL
	 *
	 * @param array $data
	 * @return string
	 */
	public static function _makeInsertValue( $value )
	{
		$keyString = implode( "` , `" , array_keys( $value ) );
		$valueString = implode( "' , '", $value );
		return "( `{$keyString}` ) VALUES ( '{$valueString}' )";
	}

	/**
	 * 拼接排序SQL
	 * @param array $order
	 * @return string
	 */
	public static function _makeOrderValue(  $order )
	{
		$orderList = array();
		foreach( $order as $key => $value )
		{
			$orderList[] =  "`{$key}` {$value}";
		}

		return implode( " , ", $orderList );
	}
}